﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.sys;
using Domain.Entity.zct;
using Domain.IRepository;

namespace EFCore.Repository
{
    public class SlotRepository : ISlotRepository
    {
        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        // 单个库位添加
        public async Task<int> AddSlot(Slot slot)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = @"INSERT INTO [dbo].[bs_slot] 
              (wh_id, slot_code, slot_name, slot_capacity, slot_userId, slot_userName, 
               slot_sort, slot_status, is_delete, remark)
              VALUES (@wh_id, @slot_code, @slot_name, @slot_capacity, @slot_userId, @slot_userName, 
               @slot_sort, @slot_status, @is_delete, @remark)";

                return await sqlConnection.ExecuteAsync(sql, new
                {
                    wh_id = slot.wh_id,
                    slot_code = slot.slot_code,
                    slot_name = slot.slot_name,
                    slot_capacity = slot.slot_capacity,
                    slot_userId = slot.slot_userId,
                    slot_userName = slot.slot_userName,
                    slot_sort = slot.slot_sort,
                    slot_status = slot.slot_status,
                    is_delete = slot.is_delete,
                    remark = slot.remark
                });
            }
        }




        // 批量添加库位
        public async Task<int> AddSlot(List<Slot> slots)
        {
            if (slots == null || slots.Count == 0)
                return 0;

            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = @"INSERT INTO [dbo].[bs_slot] 
             (wh_id, slot_code, slot_name, slot_capacity, slot_userId, 
              slot_userName, slot_status, slot_sort, remark, is_delete)
             VALUES 
             (@wh_id, @slot_code, @slot_name, @slot_capacity, @slot_userId, 
              @slot_userName, @slot_status, @slot_sort, @remark, @is_delete)";

                return await sqlConnection.ExecuteAsync(sql, slots);
            }
        }

        //库位id删除
        public async Task<int> DeleteSlot(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = @"UPDATE [dbo].[bs_slot] SET is_delete = 1 WHERE slot_id = @id";
                return await sqlConnection.ExecuteAsync(sql, new { id });
            }
        }


        //查询仓库主管信息
        public async Task<List<User>> GetAllUsers()
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "SELECT user_id, user_name, user_phone FROM [dbo].[zct_user] WHERE is_delete = 0";
                return sqlConnection.Query<User>(sql).ToList();
            }
        }

        //分页查询库位
        public async Task<(List<Slot> List, int TotalCount)> GetSlots(
            int pageSize,
            int currentPage,
            string? code,
            string? name,
            int? status,
            string? userName,
            int? warehouseId = null)
        {
            try
            {
                using (var sqlConnection = new SqlConnection(con))
                {
                    await sqlConnection.OpenAsync();

                    DynamicParameters param = new DynamicParameters();
                    // 构建WHERE条件
                    var conditions = new StringBuilder("is_delete = 0");

                    if (warehouseId.HasValue)
                    {
                        conditions.Append(" AND wh_id = @warehouseId");
                        param.Add("@warehouseId", warehouseId.Value);
                    }

                    if (!string.IsNullOrEmpty(code))
                    {
                        conditions.Append(" AND slot_code LIKE @code");
                        param.Add("@code", $"%{code}%");
                    }

                    if (status.HasValue)
                    {
                        conditions.Append(" AND slot_status = @status");
                        param.Add("@status", status.Value);
                    }

                    if (!string.IsNullOrEmpty(userName))
                    {
                        conditions.Append(" AND slot_userName LIKE @userName");
                        param.Add("@userName", $"%{userName}%");
                    }

                    string whereClause = conditions.ToString();

                    // 分页查询
                    string dataSql = $@"
                WITH PagedData AS (
                    SELECT *, ROW_NUMBER() OVER (ORDER BY slot_id) AS RowNum
                    FROM [dbo].[bs_slot]
                    WHERE {whereClause}
                )
                SELECT * FROM PagedData
                WHERE RowNum BETWEEN @startRow AND @endRow";
                    
                    // 总数查询
                    string countSql = $"SELECT COUNT(*) FROM [dbo].[bs_slot] WHERE {whereClause}";

                    int startRow = (currentPage - 1) * pageSize + 1;
                    int endRow = startRow + pageSize - 1;

                    param.Add("@startRow", startRow);
                    param.Add("@endRow", endRow);

                    // 并行执行查询
                    var queryResult = await sqlConnection.QueryMultipleAsync($"{dataSql}; {countSql}", param);

                    var list = (await queryResult.ReadAsync<Slot>()).ToList();
                    var totalCount = await queryResult.ReadSingleAsync<int>();

                    return (list, totalCount);
                }
            }
            catch (Exception ex)
            {
                Console.Error.WriteLine($"GetSlots错误: {ex.Message}");
                throw new ApplicationException("获取库位数据时发生错误，请稍后再试", ex);
            }
        }



        //根据仓库id查询对应库位
        public async Task<List<Slot>> GetSlotsByWhid(int whid)
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $"select slot_id,slot_name from [dbo].[bs_slot] where wh_id={whid} and is_delete=0";
                return sqlConnection.Query<Slot>(sql).ToList();
            }
        }

        //修改库位
        public async Task<int> UpdateSlot(Slot slot)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = @"UPDATE [dbo].[bs_slot] 
                      SET slot_code = @slot_code, 
                          slot_name = @slot_name, 
                          slot_capacity = @slot_capacity, 
                          slot_userId = @slot_userId, 
                          slot_userName = @slot_userName, 
                          slot_sort = @slot_sort, 
                          slot_status = @slot_status, 
                          remark = @remark 
                      WHERE slot_id = @slot_id";

                return await sqlConnection.ExecuteAsync(sql, new
                {
                    slot_code = slot.slot_code,
                    slot_name = slot.slot_name,
                    slot_capacity = slot.slot_capacity,
                    slot_userId = slot.slot_userId,
                    slot_userName = slot.slot_userName,
                    slot_sort = slot.slot_sort,
                    slot_status = slot.slot_status,
                    remark = slot.remark,
                    slot_id = slot.slot_id
                });
            }
        }

    }
}
